﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.ESS.MODEL;

namespace KPIS.GERP.ESS.DAL
{
    public class ComboESSDAL
    {
        public string GetJobsitenameByJobsiteseq(string job_site_seq)
        {
            DataSet ds = null;
            IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        
            try
            {
                dbManager.Open();

                string strSQL;
                strSQL = @"SELECT job_site_name from hrm_mp_job_sites where job_site_seq = " + job_site_seq;
                    ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                return Convert.ToString(ds.Tables[0].Rows[0][0]);
            }
            catch (Exception ex)
            {
                 
            }
            finally
            {
                dbManager.Dispose();
            }
            return "";
        }

        public int GetAppPrcId(int absence_type_seq)
        {
            DataSet ds = null;
            IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        
            try
            {
                dbManager.Open();

                string strSQL;
                strSQL = @"
select AppPrcId from hrm_ps_absence_types where absence_type_seq = " + absence_type_seq;
                    ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                return Convert.ToInt32(ds.Tables[0].Rows[0][0]);
            }
            catch (Exception ex)
            {
                throw ex;// new Exception(ex.Message + ", "  + SystemInfo._ConnectionString);
            }
            finally
            {
                dbManager.Dispose();
            }                                                                                                 
        }

        public DataSet Detail()
        {
            DataSet ds = null;
            IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        
            try
            {
                dbManager.Open();

                string strSQL;
                strSQL = @"
select *, '' as System from (
SELECT IIF(u.user_code IS NULL, '-- ยังไม่มี User --', u.user_code) AS UserName
	, CONCAT(np.prefix_name,e.first_name, ' ', e.last_name, ' ') AS FullName
	, MIN(IIF(m.permission_type = 'A','ใช่','ไม่ใช่')) AS Approvable
    , CONCAT(p.pos_ref,' - ',p.pos_name) AS Position
    , j.job_site_name AS 'JobSite'
	, e.emp_seq 
	, u.user_seq 
	, p.pos_seq
	, p.pos_ref
    , IIF(u.user_code IS NULL, 1, 0) AS HasUser
	, u.user_password AS PassWord
    
FROM hrm_ps_employees e
	LEFT JOIN sys_sc_users u ON e.emp_seq = u.emp_seq and u.record_status <> 'D'
	LEFT JOIN hrm_ps_employee_statuses s ON e.emp_status_seq = s.emp_status_seq and s.record_status <> 'D'
	LEFT JOIN MAS_NAME_PREFIXES np ON np.name_prefix_seq = e.name_prefix_seq and np.record_status <> 'D'
    LEFT JOIN hrm_mp_positions p ON p.pos_seq = e.pos_seq and p.record_status <> 'D'
    LEFT JOIN sys_sc_user_roles ur ON e.pos_seq = ur.pos_seq AND ur.record_status <> 'D' 
    LEFT JOIN sys_sc_permissions m ON ur.role_seq = m.role_seq AND m.record_status <> 'D' 
    LEFT JOIN hrm_mp_job_sites j ON j.job_site_seq = e.working_job_site_seq AND j.record_status <> 'D' 
	LEFT JOIN 
		(
			SELECT 
				tb.user_seq AS user_seq
			FROM (
				SELECT us.user_seq, ro.system
				FROM sys_sc_user_roles ur 
					INNER JOIN sys_sc_users us ON us.user_seq = ur.user_seq 
					INNER JOIN sys_sc_roles ro ON ro.role_seq = ur.role_seq 
				WHERE ur.record_status <> 'D'
				AND ro.record_status <> 'D'
				AND us.record_status <> 'D'
					GROUP BY us.user_seq, ro.system
			) AS tb
			GROUP BY tb.user_seq
		) tb_sys_users ON tb_sys_users.user_seq = u.user_seq
WHERE e.record_status <> 'D'
AND s.active = '1'
GROUP BY e.emp_seq, u.user_code, u.user_password, np.prefix_name, e.first_name, e.last_name
, p.pos_ref,p.pos_name, u.user_seq, p.pos_seq , j.job_site_name
) as tb2 
ORDER BY HasUser, LEN(pos_ref) , pos_ref";
                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw ex;// new Exception(ex.Message + ", "  + SystemInfo._ConnectionString);
            }
            finally
            {
                dbManager.Dispose();
            }

            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                string empSeq = Convert.ToString(ds.Tables[0].Rows[i]["Emp_Seq"]);
                if (!String.IsNullOrEmpty(empSeq))
                {
                    string jobSiteSeq = GetWorkingJobSiteByEmpSeq(empSeq);
                    string jobSiteName = GetJobSiteName(jobSiteSeq);
                    ds.Tables[0].Rows[i]["JobSite"] = jobSiteSeq + "-" + jobSiteName;
                }
            }

            {
                DataRow newCustomersRow = ds.Tables[0].NewRow();
                ds.Tables[0].Rows.Add(newCustomersRow);
            }

            {
                DataRow newCustomersRow = ds.Tables[0].NewRow();

                var jobSiteStrategyId = JobSiteStrategyID();
                var jobSiteStrategyName = GetJobSiteName(jobSiteStrategyId);
                newCustomersRow["System"] = "*หน่วยงานสำนักแผน คือ " + 
                    jobSiteStrategyId + "-" + jobSiteStrategyName;

                ds.Tables[0].Rows.Add(newCustomersRow);
            }

            {
                DataRow newCustomersRow = ds.Tables[0].NewRow();
                ds.Tables[0].Rows.Add(newCustomersRow);
            } 
            {
                DataRow newCustomersRow = ds.Tables[0].NewRow();
                newCustomersRow["System"] = "*สำนักที่มีในขณะนี้คือ";
                ds.Tables[0].Rows.Add(newCustomersRow);
            } 

            {
                DataTable listJobSite = ListJobsite();
                foreach (DataRow dr in listJobSite.Rows)
                {
                    DataRow newCustomersRow = ds.Tables[0].NewRow();

                    newCustomersRow["System"] = " ๐ " + Convert.ToString(dr["value"]) + "-" + dr["text"];

                    ds.Tables[0].Rows.Add(newCustomersRow);
                }
            }

            return ds;
        }

        public DataTable ListJobsite()
        {
            IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        
            try
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                string sql = " select job_site_seq as value, job_site_name as text"
                             + " from hrm_mp_job_sites"
                             + " where record_status=@RecordStatus"
                             + " and job_site_type_seq = 2"
                             + " and job_site_level_seq = 3"
                    //+ " and gfmis_code is not Null"
                             + " order by job_site_name";
                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public string GetJobSiteName(string job_site_seq)
        {
            IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        
            try
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@job_site_seq", job_site_seq);
                string sql = " select job_site_name"
                             + " from hrm_mp_job_sites"
                             + " where job_site_seq = @job_site_seq";

                DataTable dt;
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt.Rows[0]["job_site_name"].ToString();
            }
            catch (Exception ex)
            {

            }
            finally { dbManager.Dispose();}
            return null;
        }

        public string GetWorkingJobSiteByEmpSeq(string emp_seq)
        {
            IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

            try
            {
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@emp_seq", emp_seq);
                string sql;
                sql = " select working_job_site_seq"
                      + " from hrm_ps_employees"
                      + " where emp_seq = @emp_seq";

                DataTable dt;
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                string workingJobSiteSeq = dt.Rows[0]["working_job_site_seq"].ToString();
                string jobSiteSeq = CheckJobSiteLevel(workingJobSiteSeq);
                return jobSiteSeq;
            }
            catch (Exception ex)
            {

            }
            finally
            {
                dbManager.Dispose();}
            return null;
        }

        public string CheckJobSiteLevel(string job_site_seq)
        {
            IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        
            try
            {
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@job_site_seq", job_site_seq);
                string sql;

                sql = " select job_site_level_seq, upper_job_site_seq"
                    + " from hrm_mp_job_sites"
                    + " where job_site_seq = @job_site_seq"
                    + " and job_site_level_seq >= 3";

                dbManager.Open();
                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();

                if (dtb.Rows.Count > 0)
                {
                    string job_site_level_seq = dtb.Rows[0]["job_site_level_seq"].ToString();

                    if (job_site_level_seq == "3")
                    {
                        return job_site_seq;
                    }
                    else
                    {
                        return CheckJobSiteLevel(dtb.Rows[0]["upper_job_site_seq"].ToString());
                    }
                }
                else
                {
                    return job_site_seq;
                }


                return "";
            }
            catch (Exception ex)
            {

            }
            finally
            {
                dbManager.Dispose();
            }

            return null;
        }


        public string JobSiteStrategyID()
        {
            IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        
            try
            {
                string strResult = "";
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@bgm_sys_control_name", "job_site_strategy");
                string sql = " select bgm_sys_control_values "
                           + " from bgm_system_control"
                           + " where bgm_sys_control_name like @bgm_sys_control_name";
                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];

                if (dt.Rows.Count > 0)
                {
                    strResult = dt.Rows[0][0].ToString();
                }

                return strResult;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }


        public DataTable LoadPositionWorkingJobsites(Nullable<int> EmpTypeSeq, string ParentID)
        {
            IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        
            DataTable result;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(8); 

                dbManager.AddParameters(0, "@EmpTypeSeq", EmpTypeSeq);
                dbManager.AddParameters(1, "@JobSiteTypeSeq", "2" /*SystemInfo._JobsiteInternal*/);
                dbManager.AddParameters(2, "@JobSiteUpper", "2" /*SystemInfo._JobsiteInternal*/);
                dbManager.AddParameters(3, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(4, "@CancelRecord", SystemInfo._CancelRecordStatus);
                dbManager.AddParameters(5, "@ActiveRecord", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(6, "@TypeName", "โครงสร้างตามการบริหารจัดการภายใน");
                dbManager.AddParameters(7, "@parent_id", ParentID);

                string strSQL = @"SELECT tb1.ID
                                    , tb1.pos_ref
                                    , tb1.pos_name
                                    , tb1.exec_pos_name
                                    , IF(pos1.record_status = @ActiveRecord
                                        , tb1.parent_ID
                                        , IF(tb1.parent_ID is null, null, 0)
                                      ) as parent_ID 
                                    , IF(pos1.record_status = @ActiveRecord OR tb1.parent_ID = 0 OR tb1.parent_ID is null
                                        , tb1.emp_name
                                        , CONCAT(tb1.emp_name, ' [ตำแหน่งที่เหนือกว่าในสายบังคับบัญชาถูกลบ]')
                                      ) as emp_name 
                                    , tb1.job_site_name
                                    , tb1.emp_type_name
                                    , tb1.internal_job_site_seq
                                    , tb1.internal_job_site_name 
                                    , CONCAT(pos1.record_status , tb1.parent_ID) as parentrecordstatusandid
                                    , if(tb1.exec_pos_name <> '', CONCAT('(', tb1.exec_pos_name , ')'), '') as exec_pos_name_parentheses
                                    , tb1.emp_seq
                                  FROM (
                                    select 0 as ID, '' as pos_ref, 'สายการบังคับบัญชา' as pos_name 
                                        , 'โครงสร้างบริหารจัดการภายใน' as exec_pos_name
                                        , null as parent_ID
                                        , '' as emp_name
                                        , '' as job_site_name
                                        ,'' as emp_type_name
                                        ,'' as internal_job_site_seq
                                        ,'' as internal_job_site_name
                                        , '' as emp_seq

                                    union all

                                    select p.pos_seq as ID, p.pos_ref, p.pos_name, exe.exec_pos_name
                                        , coalesce(p.pos_working_upper_seq, 0) as parent_ID
                                        , case when e.emp_seq is null
                                        then 'ไม่มีคนครอง'
                                        else cast(concat(np.prefix_short, e.first_name, ' ', e.last_name) as char)
                                        end as emp_name
                                        , j.job_site_name, et.emp_type_name
                                        , j_internal.job_site_seq as internal_job_site_seq
                                        , IF(j_internal.job_site_name is null OR j_internal.job_site_name = '' 
                                            ,'-'
                                            ,j_internal.job_site_name
                                          ) as internal_job_site_name
                                        , e.emp_seq as emp_seq
                                    from hrm_mp_positions p
                                        left join hrm_ps_employee_types et 
                                            on p.emp_type_seq = et.emp_type_seq
                                        left join hrm_mp_exec_positions exe 
                                            on p.exec_pos_seq = exe.exec_pos_seq
                                        left join hrm_ps_employees e 
                                            on p.pos_seq = e.pos_seq
                                                and e.record_status not in (@DeleteRecord, @CancelRecord)
                                                and e.emp_status_seq in (
                                                    select emp_status_seq from hrm_ps_employee_statuses where active = '1'
                                                )
                                        left join mas_name_prefixes np 
                                            on e.name_prefix_seq = np.name_prefix_seq
                                        left join hrm_mp_job_sites j 
                                            on p.job_site_seq = j.job_site_seq                                
                                        LEFT JOIN hrm_mp_job_sites j_internal 
                                            ON e.working_job_site_seq = j_internal.job_site_seq
                                    where p.record_status not in (@DeleteRecord, @CancelRecord)
                                 ) as tb1
                                    LEFT JOIN hrm_mp_positions pos1 
                                        ON pos1.pos_seq = tb1.parent_ID ";
                result = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception error)
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
            return result;
        }
    }
}
